using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TienTeTuong;
using log4net;

namespace TienTeTuong
{
	/// <summary>
	/// Data access layer class for Articlecategory
	/// </summary>
	class ArticlecategoryDAO 
	{
        public List<ArticlecategoryDTO> GetListArticlecategory()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticlecategoryDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM articlecategory";
             
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticlecategoryDTO();
                        cate.ArtCateID = reader[0].ToString();
                        cate.CateName = reader[1].ToString();
                        cate.Description = reader[2].ToString();
                        cate.CateParent = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[6].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[7].ToString());
                     
                        lstArticle.Add(cate);

                    }
                    catch (Exception)
                    {

                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }
        public ArticlecategoryDTO GetArtCateByID(string ArtCateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;

            var cate = new ArticlecategoryDTO();
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM articlecategory Where ArtCateID='" + ArtCateID + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    cate.ArtCateID = reader[0].ToString();
                    cate.CateName = reader[1].ToString();
                    cate.Description = reader[2].ToString();
                    cate.CateParent = reader[3].ToString();
                    cate.VipLevel = int.Parse(reader[4].ToString());
                    cate.Active = reader[5].ToString() == "1";
                    cate.CreatedDate = Convert.ToDateTime(reader[6].ToString());
                    cate.UpdatedDate = Convert.ToDateTime(reader[7].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return cate;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return cate;
            }
        }
        public List<ArticlecategoryDTO> searchCategoryByName(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<ArticlecategoryDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM articlecategory where articlecategory.CateName Like '%" + key + "%' Order by articlecategory.CateName asc";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticlecategoryDTO();

                        cate.ArtCateID = reader[0].ToString();
                        cate.CateName = reader[1].ToString();
                        cate.Description = reader[2].ToString();
                        cate.CateParent = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[6].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[7].ToString());

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        public bool delete(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                //var strSQL = @"UPDATE `tientetuong`.`category` SET `Active` = b '0' WHERE `category`.`CateID` = '" + key + "'";
                var strSQL = @"Delete FROM `tientetuong`.`articlecategory` where `articlecategory`.`ArtCateID` = '" + key + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool update(ArticlecategoryDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"UPDATE `articlecategory` SET  `CateName` =  '" + item.CateName + "', "
               + "`Description` =  '" + item.Description + "', "
               + "`CateParent` =  '" + item.CateParent + "', "
               + "`Viplevel` =  '" + item.VipLevel + "',"
               + "`Active` =  b'" + (bool.Parse(item.Active.ToString()) ? "1" : "0") + "'"
               + " WHERE  `articlecategory`.`ArtCateID` =  '" + item.ArtCateID + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool add(ArticlecategoryDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Insert into `articlecategory` (`ArtCateID`, `CateName`, `Description`, `VipLevel`, `Active`, `CateParent`, `LunarDate`)"
                    + " Values ('" + item.ArtCateID + "', '" + item.CateName + "', '" + item.Description + "', '" + item.VipLevel + "', b'" + ((bool)item.Active ? "1" : "0") + "', '" + item.CateParent
                    + "', '" + item.UpdatedDate + "');";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

	}
}
